package com.the_ring.chapter05.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import com.the_ring.chapter05.bean.UserInfo;

import java.util.ArrayList;
import java.util.List;

public class UserHelper extends SQLiteOpenHelper {

    private static final String DB_NME = "user.db";
    private static UserHelper userHelper = null;
    private SQLiteDatabase database = null;
    protected static final String TABLE_NAME = "user_info";


    // 利用单例模式获取数据帮助器的唯一实例
    public static UserHelper getInstance(Context context, int version) {
        if (version > 0 && userHelper == null) {
            userHelper = new UserHelper(context, version);
        }
        return userHelper;
    }


    public UserHelper(@Nullable Context context, int version) {
        super(context, DB_NME, null, version);
    }

    // 创建数据库，执行建表语句
    @Override
    public void onCreate(SQLiteDatabase db) {
        String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "(" +
                "phone VARCHAR NOT NULL," +
                "pwd VARCHAR NOT NULL)";
        db.execSQL(create_sql);
    }

    // 升级数据库，执行表结构变更语句
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}

    // 打开数据库的读连接
    public SQLiteDatabase openReadLink() {
        if (database == null || !database.isOpen()) {
            database = userHelper.getReadableDatabase();
        }
        return database;
    }

    // 打开数据库的写连接
    public SQLiteDatabase openWriteLink() {
        if (database == null || !database.isOpen()) {
            database = userHelper.getWritableDatabase();
        }
        return database;
    }

    // 关闭数据库连接
    public void closeLink() {
        if (database != null && database.isOpen()) {
            database.close();
            database = null;
        }
    }

    // 根据指定条件删除表记录
    public int delete(String condition) {
        // 执行删除记录操作，该语句返回删除记录的数目
        return database.delete(TABLE_NAME, condition, null);
    }

    // 往该表添加多条记录
    public long insert(List<UserInfo> infoList) {
        long result = -1;
        for (int i = 0; i < infoList.size(); i++) {
            UserInfo info = infoList.get(i);
            // 不存在唯一性重复的记录，则插入新记录
//            ContentValues cv = new ContentValues();
//            cv.put("phone", info.getPhone());
//            cv.put("pwd", info.getPwd());
//            // 指定插入记录操作，该语句返回插入记录的行号
//            result = database.insert(TABLE_NAME, "", cv);
            result = insert(info);
            if (result == -1) { // 添加成功则返回行号，添加失败则返回 -1
                return result;
            }
        }
        return result;
    }

    public long insert(UserInfo info) {
        ContentValues cv = new ContentValues();
        cv.put("phone", info.getPhone());
        cv.put("pwd", info.getPwd());
        return database.insert(TABLE_NAME, "", cv);
    }

    // 根据条件更新指定的表记录
    public int update(UserInfo info, String condition) {
        ContentValues cv = new ContentValues();
        cv.put("phone", info.getPhone());
        cv.put("pwd", info.getPwd());
        return database.update(TABLE_NAME, cv, condition, null);
    }

    // 根据指定条件查询记录，并返回结果数据列表
    public List<UserInfo> query(String condition) {
        String sql = String.format("select phone, pwd " +
                "from %s where %s;", TABLE_NAME, condition);
        List<UserInfo> infoList = new ArrayList<>();
        // 指定记录查询动作，该语句返回结果集的游标
        Cursor cursor = database.rawQuery(sql, null);
        // 循环去除游标指向的每条记录
        while (cursor.moveToNext()) {
            UserInfo info = new UserInfo();
            info.setPhone(cursor.getString(0));
            info.setPwd(cursor.getString(1));
            infoList.add(info);
        }
        cursor.close();
        return infoList;
    }
}
